package cn.pdx.elec.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;


import cn.pdx.elec.dao.ElecUserRoleDao;

import cn.pdx.elec.domain.ElecUserRole;

/**
 *  @Repository 、@Service 和 @Controller 
     在目前的 Spring 版本中，这 3 个注释和 @Component 是等效的，但是从注释类的命名上，3 个注释分别和持久层 、业务层 和控制层 相对应。
* @author 彭定旭
 *
 */
//或者这样@Repository("cn.pdx.elec.dao.impl.ElecTextDaoImpl")

/**
 * elec_user_role用户和角色关联
 * @author Administrator
 *
 */

@Repository(ElecUserRoleDao.SERVICE_NAME)
public class ElecUserRoleDaoImpl extends CommonDaoImpl<ElecUserRole> implements ElecUserRoleDao {

	
	/**
	 * @Name:findElecUserListByRoleID
	 * @Description:使用角色ID查询该角色下该有的权限，并与系统中所有的权限匹配
	 *              使用角色ID查询该角色下所有用户
	 * @author 彭定旭
	 * @version：v1.00（版本号）
	 * @Create Date:2013-4-15(创建日期)
	 * @parameters:String roleID
	 * @return:List<Object[]>用户集合
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> findElecUserListByRoleID(final String roleID) {
		
		/**
		 *  select distinct case elec_user_role.roleID 
            when '1' then '1' else '0' end as flag, 
			elec_user.userID, 
			elec_user.userName, 
			elec_user.logonName 
			from elec_user 
			left outer join elec_user_role 
			on elec_user.userID=elec_user_role.userID 
			and elec_user_role.roleID='1' 
			where elec_user.isDuty='1';
		 */
		
		//妹的;要注意空格
		final   String  sql="select distinct case elec_user_role.roleID " +
				            "when ? then '1' else '0' end as flag," +
				             "elec_user.userID as userID ," +
				             "elec_user.userName as userName, " +
				             "elec_user.logonName as logonName " +
				             " from elec_user " +
				             "left outer join elec_user_role " +
				             "on elec_user.userID=elec_user_role.userID " +
				             "and elec_user_role.roleID= ? " +
				             "where elec_user.isDuty='1' ";
		
		  List<Object []> list=(List<Object[]>) this.getHibernateTemplate().execute(new HibernateCallback() {
			
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				
				//使用标量是为了统一成string类型
				Query query=session.createSQLQuery(sql)
				            .addScalar("flag",Hibernate.STRING)
							.addScalar("userID",Hibernate.STRING)
							.addScalar("userName",Hibernate.STRING)
							.addScalar("logonName",Hibernate.STRING);
				
				query.setString(0, roleID);
				query.setString(1, roleID);
				
				return query.list();
			}
		});
		return list;
	}

	
}
